﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace OleDBCommon
{    
    public class OleDBObject
    {
        private static string strConnectionString;
        static private object objectLock = new object();
        public static string StrConnectionString
        {
            get {
                if (strConnectionString == null || strConnectionString.Length == 0 || strConnectionString == "")
                {
                    lock (objectLock)
                    {
                        //string sqlc = System.Web.HttpContext.Current.Server.MapPath("..") + System.Configuration.ConfigurationManager.AppSettings["oleconnectionString"];
                        string sqlc = System.Configuration.ConfigurationManager.AppSettings["oleconnectionString"];

                        if (sqlc != null && sqlc.Length > 0)
                            strConnectionString = sqlc;

                    }
                }
                return OleDBObject.strConnectionString;
            }       
        }
         static OleDBObject()
        { }
        public static DataTable GetTableList()
        {
            OleDbConnection con = new OleDbConnection(Util.OleDbHelper.ConnectionString);
            DataTable dt = new DataTable();
            if (con.State == System.Data.ConnectionState.Closed)
            {
                con.Open();
            }         

            try
            {
                dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null,null,null,"TABLE" });             
            }
            catch (Exception ex)
            {             
                throw ex;
            }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                {
                    con.Close();
                }

                con.Dispose();

            }
            return dt;
        }
        public static DataTable GetColumnList()
        {
            OleDbConnection con = new OleDbConnection(Util.OleDbHelper.ConnectionString);
            DataTable dt = new DataTable();
            if (con.State == System.Data.ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, null, null });
                dt.Columns.Add("TypeName");
                dt.Columns.Add("OleTypeName");
                dt.AcceptChanges();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["TypeName"] = GetDataType(Convert.ToInt32(dt.Rows[i]["DATA_TYPE"]));
                    dt.Rows[i]["OleTypeName"] = GetOleDataType(Convert.ToInt32(dt.Rows[i]["DATA_TYPE"]));
                }
                dt.DefaultView.Sort = " ORDINAL_POSITION ASC";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                {
                    con.Close();
                }

                con.Dispose();

            }
            return dt.DefaultView.ToTable();
        }
        public static DataTable GetColumnList(string tableName)
        {
            OleDbConnection con = new OleDbConnection(Util.OleDbHelper.ConnectionString);
            DataTable dt = new DataTable();
            if (con.State == System.Data.ConnectionState.Closed)
            {
                con.Open();
            }

            try
            {
                dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null,tableName, null });
                dt.Columns.Add("TypeName");          
                dt.Columns.Add("OleTypeName");
                dt.AcceptChanges();
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["TypeName"] = GetDataType(Convert.ToInt32(dt.Rows[i]["DATA_TYPE"]));
                    dt.Rows[i]["OleTypeName"] = GetOleDataType(Convert.ToInt32(dt.Rows[i]["DATA_TYPE"]));
                }
                dt.DefaultView.Sort = " ORDINAL_POSITION ASC";
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                {
                    con.Close();
                }

                con.Dispose();

            }
            return dt.DefaultView.ToTable();
        }
        public static string GetDataType(int typeCode)
        {     
           
            switch (typeCode)
            {
                case 2:
                    return "short";
                case 3:
                    return "int";
                case 4:
                    return "single";
                case 5:
                    return "double";
                case 6:
                    return "double";
                case 7:
                    return "datetime";
                case 11:
                    return "bool";
                case 17:
                    return "byte";
                case 128:
                    return "oldobejct";
                case 130:
                    return "string";
                case 131:
                    return "littlenum";
                default:
                    return "";
                    break;
            }
        }
        public static string GetOleDataType(int typeCode)
        {

            switch (typeCode)
            {
                case 2:
                    return "SmallInt";
                case 3:
                    return "Integer";
                case 4:
                    return "Single";
                case 5:
                    return "Double";
                case 6:
                    return "Double";
                case 7:
                    return "Date";
                case 11:
                    return "Boolean";
                case 17:
                    return "Binary";
                case 128:
                    return "oldobejct";
                case 130:
                    return "VarChar";
                case 131:
                    return "Single";
                default:
                    return "";
                    break;
            }
        }


        //        [DbToCS]
        //varchar=string
        //varchar2=string
        //nvarchar=string
        //nvarchar2=string
        //char=string
        //nchar=string
        //text=string
        //longtext=string
        //ntext=string
        //string=string
        //date=DateTime
        //datetime=DateTime
        //smalldatetime=DateTime
        //smallint=int
        //int=int
        //number=int
        //bigint=long
        //tinyint=int
        //float=decimal
        //numeric=decimal
        //decimal=decimal
        //money=decimal
        //smallmoney=decimal
        //real=decimal
        //bit=bool
        //binary=byte[]
        //varbinary=byte[]
        //image=byte[]
        //raw=byte[]
        //long=byte[]
        //long raw=byte[]
        //blob=byte[]
        //bfile=byte[]
        //uniqueidentifier=Guid
        //integer=int
        //double=decimal
        //enum=Enum
        //timestamp=DateTime
        //[ToSQLProc]
        //varchar=VarChar
        //string=VarChar
        //nvarchar=NVarChar
        //char=Char
        //nchar=NChar
        //text=Text
        //ntext=NText
        //datetime=DateTime
        //smalldatetime=SmallDateTime
        //smallint=SmallInt
        //tinyint=TinyInt
        //int=Int
        //bigint=BigInt
        //float=Float
        //real=Real
        //numeric=Decimal
        //decimal=Decimal
        //money=Money
        //smallmoney=SmallMoney
        //bool=Bit
        //bit=Bit
        //binary=Binary
        //varbinary=VarBinary
        //image=Image
        //uniqueidentifier=UniqueIdentifier
        //timestamp=Timestamp
        //[ToOraProc]
        //char=Char
        //varchar2=VarChar
        //string=VarChar
        //nvarchar2=NVarChar
        //nchar=NChar
        //long=LongVarChar
        //number=Number
        //int=Number
        //date=DateTime
        //raw=Raw
        //long raw=LongRaw
        //blob=Blob
        //bit=Clob
        //clob=Clob
        //nclob=NClob
        //bfile=BFile
        //[ToMySQLProc]
        //binary=Binary
        //bool=Bit
        //bit=Bit
        //blob=Blob
        //double=Double
        //Date=DateTime
        //datetime=DateTime
        //numeric=Decimal
        //decimal=Decimal
        //float=Float
        //enum=Enum
        //geometry=Geometry
        //longBlob=LongBlob
        //varchar=VarChar
        //string=String
        //char=VarChar
        //text=Text
        //longtext=LongText
        //time=Time
        //SmallInt=Int32
        //TinyInt=Int32
        //timestamp=Timestamp
        //tinyText=TinyText
        //tinyBlob=TinyBlob
        //int=Int32
        //varbinary=VarBinary
        //varstring=VarString
        //year=Year
        //varchar=VarChar
        //[ToOleDbProc]
        //varchar=VarChar
        //string=VarChar
        //nvarchar=LongVarChar
        //char=Char
        //nchar=NChar
        //text=LongVarChar
        //ntext=LongVarChar
        //datetime=Date
        //smalldatetime=Date
        //smallint=SmallInt
        //tinyint=TinyInt
        //int=Integer
        //bigint=BigInt
        //money=Decimal
        //smallmoney=Decimal
        //float=Decimal
        //numeric=Decimal
        //decimal=Decimal
        //bool=Boolean
        //bit=Bit
        //binary=Binary
        //[IsAddMark]
        //nvarchar=true
        //nchar=true
        //ntext=true
        //varchar=true
        //varchar2=true
        //nvarchar2=true
        //char=true
        //clob=true
        //string=true
        //text=true
        //date=true
        //datetime=true
        //smalldatetime=true
        //uniqueidentifier=true
        //[isValueType]
        //int=true
        //Int32=true
        //Int16=true
        //Int64=true
        //DateTime=true
        //decimal=true
        //Decimal=true
        //[AccessDbTypeMap]
        //2=short
        //3=int
        //4=single
        //5=double
        //6=money
        //7=datetime
        //11=bit
        //17=byte
        //130=varchar
        //131=float


    }
}
